package lcc.linkshare;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;


public class ConnectionManager {

	
	public static void insertOffer(Offer offer) throws SQLException {

		Connection conn = null;
		CallableStatement cStmt = null;

		try {
			conn = DBUtils.getDBConnection();
			cStmt = conn.prepareCall("{call DISCOUNT_USER.insertcouponfeed_pkg.InsertUpdateDISCOUNTS_LNKSHR(?,?,?,?,?,?,?,?,?,?,?)}");
			cStmt.setString(1, offer.getAdvertisername());
			cStmt.setString(2, offer.getAdvertiserID());
			cStmt.setString(3, offer.getOfferdescription());
			cStmt.setString(4, offer.getOfferstartdate());
			cStmt.setString(5, offer.getOfferenddate());
			cStmt.setString(6, offer.getImpressionpixel());
			cStmt.setString(7, offer.getNetworkid());
			cStmt.setString(8, offer.getNetwork());
			cStmt.setString(9, offer.getCouponcode());
			cStmt.setString(10, offer.getOfferid());
			cStmt.setString(11, offer.getClickurl());
			cStmt.execute();

			ArrayList<Category> categories = offer.getCategories();
			for (Category category : categories) {
				cStmt = conn.prepareCall("{call DISCOUNT_USER.insertcouponfeed_pkg.InsertUpdateLNKSHR_SUBCAT(?,?,?)}");
				cStmt.setString(1, category.getCategoryID());
				cStmt.setString(2, category.getCategory());
				cStmt.setString(3, offer.getOfferid());
				cStmt.execute();
			}
			
			
			ArrayList<Promotiontype> promotiontypes = offer.getPromotiontypes();
			for (Promotiontype promotiontype : promotiontypes) {
				cStmt = conn.prepareCall("{call DISCOUNT_USER.insertcouponfeed_pkg.InsertUpdateLNKSHR_PROMO(?,?,?)}");
				cStmt.setString(1, promotiontype.getPromotiontypeID());
				cStmt.setString(2, promotiontype.getPromotiontype());
				cStmt.setString(3, offer.getOfferid());
				cStmt.execute();
			}

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			DBUtils.cleanUp(conn, cStmt);
		}
	}
}
